﻿CREATE TABLE [dbo].[tblMaint_Security]
(
	[secId] [int] IDENTITY(1,1) NOT NULL,
	[secDbProject] [varchar](256) NOT NULL CONSTRAINT [PK_tblMaint_Security_DbProject] DEFAULT ('SYSTEM'),
	[secUserName] [varchar](128) NOT NULL,
	[secSid] varbinary(256) NULL,
	[secServerRole] [varchar](256) NULL,
	[secServerPermission] [varchar](512) NULL,
	[secDbName] [varchar](128) NULL,
	[secDbRole] [varchar](128) NULL, 
	[secObjectType]  AS (case when NOT [secServerPermission] like '% ON %' then NULL when [secServerPermission] like '% ON %' AND NOT [secServerPermission] like '%::%' then 'OBJECT' else ltrim(rtrim(substring([secServerPermission],(4)+charindex(' ON ',[secServerPermission]),charindex('::',[secServerPermission])-((4)+charindex(' ON ',[secServerPermission]))))) end) PERSISTED,
	[secObjectName]  AS (case when NOT [secServerPermission] like '% ON %' then NULL when [secServerPermission] like '% ON %' AND NOT [secServerPermission] like '%::%' then ltrim(rtrim(substring([secServerPermission],(4)+charindex(' ON ',[secServerPermission]),len([secServerPermission])))) else ltrim(rtrim(replace(replace(substring([secServerPermission],(2)+charindex('::',[secServerPermission]),len([secServerPermission])),'[',''),']',''))) end) PERSISTED,
    [secHshPwd] VARBINARY(512) NULL, 
    CONSTRAINT [PK_tblMaint_Security] PRIMARY KEY ([secId])
);
GO
CREATE INDEX [IX_tblMaint_Security_Project] ON [dbo].[tblMaint_Security] ([secDbProject]);
